package com.jxc.jxc01.hch.dao.Impl;

import com.jxc.jxc01.hch.dao.InventoryDao;
import com.jxc.jxc01.hch.entity.Inventory;
import com.jxc.jxc01.hch.entity.JDBCConn;
import com.jxc.jxc01.hch.entity.PageHelper;
import com.jxc.jxc01.hch.entity.WareHouse;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class InventoryDaoImpl implements InventoryDao {
    /**
     * 数据加载
     * @param pageHelper
     * @return
     */
    @Override
    public PageHelper<Inventory> list(PageHelper<Inventory> pageHelper) {
        List<Inventory> list = new ArrayList<>();
        try {
            //加载驱动类
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select e.*,c.warehousename from warehouse c right outer join inventory e on c.id=e.warehouseid limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setInt(1,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(2,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory();
                inventory.setId(rs.getInt("id"));
                inventory.setName(rs.getString("name"));
                inventory.setCategory(rs.getString("category"));
                inventory.setInventorynumber(rs.getInt("inventorynumber"));
                inventory.setUnit(rs.getString("unit"));
                inventory.setCreationtime(rs.getString("creationtime"));
                inventory.setModificationtime(rs.getString("modificationtime"));
                inventory.setRemarks(rs.getString("remarks"));
                inventory.setWarehousename(rs.getString("warehousename"));
                list.add(inventory);

            }
            pageHelper.setData(list);
            String sql1="select count(*) from warehouse c right outer join inventory e on c.id=e.warehouseid;";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;
    }

    @Override
    public PageHelper<Inventory> query(PageHelper<Inventory> pageHelper, String username) {
        List<Inventory> list = new ArrayList<>();
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql =  "select e.*,c.warehousename from warehouse c right outer join inventory e on c.id=e.warehouseid where name like ? limit ?,?";
            PreparedStatement stat=jdbcConn.connect(sql);
            stat.setString(1,"%"+username+"%");
            stat.setInt(2,
                    (pageHelper.getPageNum()-1)*pageHelper.getPageSize());
            stat.setInt(3,pageHelper.getPageSize());
            ResultSet rs= stat.executeQuery();
            while (rs.next()){
                Inventory inventory = new Inventory();
                inventory.setId(rs.getInt("id"));
                inventory.setName(rs.getString("name"));
                inventory.setCategory(rs.getString("category"));
                inventory.setInventorynumber(rs.getInt("inventorynumber"));
                inventory.setUnit(rs.getString("unit"));
                inventory.setCreationtime(rs.getString("creationtime"));
                inventory.setModificationtime(rs.getString("modificationtime"));
                inventory.setRemarks(rs.getString("remarks"));
                inventory.setWarehousename(rs.getString("warehousename"));
                list.add(inventory);
            }
            pageHelper.setData(list);
            String sql1="select count(*) from warehouse c right outer join inventory e on c.id=e.warehouseid where name like ?";
            PreparedStatement stat1 = jdbcConn.connect(sql1);
            stat1.setString(1,"%"+username+"%");
            ResultSet rs1 = stat1.executeQuery();
            if (rs1.next()){
                int count =rs1.getInt("count(*)");
                pageHelper.setTotal(count);
            }
            rs1.close();
            stat1.close();
            rs.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return pageHelper;
    }

    /**
     * 添加用户
     * @param inventory
     * @return
     */
    @Override
    public int increase(Inventory inventory) {
        int up=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql1 ="select id from warehouse where warehousename=?";
            PreparedStatement stat1=jdbcConn.connect(sql1);
            stat1.setString(1,inventory.getWarehousename());
            ResultSet rs = stat1.executeQuery();
            int warehouseid=0;
            if (rs.next()){
                warehouseid=rs.getInt("id");
            }
            String sql ="insert into inventory(id,name,category,inventorynumber,unit,warehouseid,Creationtime,modificationtime, remarks) values (?,?,?,?,?,?,?,?,?)";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,inventory.getId());
            stat.setString(2,inventory.getName());
            stat.setString(3,inventory.getCategory());
            stat.setInt(4,inventory.getInventorynumber());
            stat.setString(5,inventory.getUnit());
            stat.setInt(6,warehouseid);
            stat.setString(7,inventory.getCreationtime());
            stat.setString(8,inventory.getModificationtime());
            stat.setString(9,inventory.getRemarks());
            up = stat.executeUpdate();
            rs.close();
            stat1.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        //创建连接对象
        return up;
    }

    @Override
    public int modify(Inventory inventory) {
        int up;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql1 ="select id from warehouse where warehousename=?";
            PreparedStatement stat1=jdbcConn.connect(sql1);
            stat1.setString(1,inventory.getWarehousename());
            ResultSet rs = stat1.executeQuery();
            int warehouseid=0;
            if (rs.next()){
                warehouseid=rs.getInt("id");
            }
            String sql = "update inventory set name=?,category=?,inventorynumber=?,unit=?,warehouseid=?,modificationtime=?,remarks=? where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setString(1,inventory.getName());
            stat.setString(2,inventory.getCategory());
            stat.setInt(3,inventory.getInventorynumber());
            stat.setString(4,inventory.getUnit());
            stat.setInt(5,warehouseid);
            stat.setString(6,inventory.getModificationtime());
            stat.setString(7,inventory.getRemarks());
            stat.setInt(8,inventory.getId());
            up = stat.executeUpdate();
            rs.close();
            stat1.close();
            stat.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return up;
    }

    @Override
    public int delete(int id) {
        int delete=0;
        try {
            JDBCConn jdbcConn = new JDBCConn();
            String sql = "delete from inventory where id=?";
            PreparedStatement stat = jdbcConn.connect(sql);
            stat.setInt(1,id);
            delete= stat.executeUpdate();
            stat.close();
        }catch (Exception e){

        }
        return delete;
    }

}
